<?php

/**
 * Description of UtenteDataManager
 *
 * @author rocco
 */
require_once 'Utente.php';
require_once '../classes/Nazione.php';
require_once '../classes/Disciplina.php';

class UtenteDataManager
{

    private static $instance;

    public static function getInstance()
    {
        if (self::$instance == null)
        {
            $c = __CLASS__;
            self::$instance = new $c;
        }
        return self::$instance;
    }

    public function getUtenti($connection, $email, $nome, $cognome, $listaDiscipline, $context)
    {
        $lista = array();
        try
        {
            $query = "Select codice, creationDate, mobile, email, nome, cognome, attivo ";
            if($listaDiscipline != '')
            {
                $query = $query . " from Utente, DisciplinaUtente ";
                $query = $query . " where Utente.codice = DisciplinaUtente.codiceUtente AND ";
            }
            else
            {
                $query = $query . " from Utente where ";

            }

            $query = $query . " context = " . $context;

            if ($email != '')
                $query = $query . " AND UPPER(email) like '%" . strtoupper(addslashes ($email)) . "%'  ";

            if ($nome != '')
                $query = $query . " AND UPPER(nome) like '%" . strtoupper(addslashes ($nome)) . "%'   ";

            if ($cognome != '')
                $query = $query . " AND UPPER(cognome) like '%" . strtoupper(addslashes ($cognome)) . "%'   ";


            if($listaDiscipline != '')
                $query = $query . " AND DisciplinaUtente.codiceDisciplina in(". $listaDiscipline.")" ;
//            if($listaDiscipline != '' && $email == '' && $nome == '' && $cognome == '')
//              $query = $query . " AND DisciplinaUtente.codiceDisciplina in(". $listaDiscipline.")" ;
//            else
//              if($listaDiscipline != '')
//                $query = $query . "  DisciplinaUtente.codiceDisciplina in(". $listaDiscipline.")" ;


            $query = $query . " order by cognome";

            //echo 'query ' . $query;
            $stmt = $connection->prepare($query);
            $stmt->execute();

            $stmt->bind_result($codice, $creationDate, $mobile, $email, $nome, $cognome, $attivo);
            while ($stmt->fetch())
            {
                $utente = new Utente();
                $utente->codice = $codice;
                $utente->creationDate = $creationDate;
                $utente->mobile = $mobile;
                $utente->email = $email;
                $utente->nome = $nome;
                $utente->cognome = $cognome;
                $utente->attivo = $attivo;
                array_push($lista, $utente);
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }

    public function updateUtente($connection, $codiceUtente, $attivo, $nome, $cognome, $nazione, $regione, $provincia,
              $comune, $mobile, $indirizzo, $cap)
    {
        $result = "0";
        try
        {
            $query = "update Utente set
                attivo = ?, nome = ?, cognome = ?, nazione = ?, regione = ?, provincia = ?, comune = ?, mobile = ?, indirizzo = ?, cap = ? 
                where codice = ?";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("isssiiissii", $attivo, $nome, $cognome, $nazione, $regione, $provincia, $comune, $mobile, $indirizzo, $cap, $codiceUtente))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        }
        catch (Exception $ex)
        {
            error_log($ex->getTrace());
            $result = "1";
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }
    public function getUtente($connection, $codice)
    {
        $utente = new Utente();
        try
        {
            $query = "Select  codice, email,
                nome,
                cognome,
                mobile,
                indirizzo,
                nazione,
                regione,
                provincia,
                comune,
                cap,
                tipoUtente, attivo
                from Utente
                where codice = ?";

            $stmt = $connection->prepare($query);
            $stmt->bind_param("i", $codice);
            $stmt->execute();

            $stmt->bind_result($codice, $email, $nome, $cognome, $mobile, $indirizzo, $nazione, $regione, $provincia, $comune, $cap, $tipoUtente, $attivo);
            if ($stmt->fetch())
            {
                $utente->codice = $codice;
                $utente->email = $email;
                $utente->nome = $nome;
                $utente->cognome = $cognome;
                $utente->mobile = $mobile;
                $utente->indirizzo = $indirizzo;
                $utente->nazione = $nazione;
                $utente->regione = $regione;
                $utente->provincia = $provincia;
                $utente->comune = $comune;
                $utente->cap = $cap;
                $utente->tipoUtente = $tipoUtente;
                $utente->attivo = $attivo;
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $utente;
    }
    public function insertUtente($connection, $obj)
    {
        $codice = -1;

        try
        {
            $query = "Insert into Utente (
                email,
                password,
                nome,
                cognome,
                mobile,
                indirizzo,
                nazione,
                regione,
                provincia,
                comune,
                cap,
                tipoUtente, attivo, context) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
            if (!$stmt1 = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt1->bind_param("sssssssiiiiiii", $obj->email, $obj->password, $obj->nome, $obj->cognome, $obj->mobile, $obj->indirizzo, $obj->nazione, $obj->regione, $obj->provincia, $obj->comune, $obj->cap, $obj->tipoUtente, $obj->attivo, $obj->context))
                throw new Exception("Errore in bind_param query : " . $query);
            if (!$stmt1->execute())
                throw new Exception("Errore in execute query : " . $query);
            $codice = $stmt1->insert_id;

        } catch (Exception $ex)
        {
            echo $ex->getMessage();


//            echo $obj->email .' ' . $obj->password .' ' .  $obj->nome .' ' .  $obj->cognome .' ' .  $obj->mobile .' ' .  $obj->indirizzo .' ' .  $obj->nazione .' ' .  $obj->regione .' ' .  $obj->provincia .' ' .  $obj->comune .' ' .  $obj->cap .' ' .  $obj->tipoUtente .' ' .  $obj->attivo;
//
//            $header = "From: Admin<info@softwareeasy.it>\n";
//            $oggetto = "errore in UtenteDataManager:insertUtente - utente : " . $_COOKIE["codiceUtente"];
//            $messaggio = " Message : " . $ex->getMessage() . " trace " . $ex->getTraceAsString();
//            mail("casaranese@gmail.com", $oggetto, $messaggio, $header);
        }

        if ($stmt != NULL)
            $stmt->close();

        return $codice;
    }

    public function insertDisciplinaUtente($connection, $codiceUtente, $codiceDisciplina)
    {
        $result = 0;
        try
        {
            $query = "Insert into DisciplinaUtente (
                codiceUtente,
                codiceDisciplina) VALUES(?, ?) ";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("ii", $codiceUtente, $codiceDisciplina))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        } catch (Exception $ex)
        {
            echo $ex->getMessage();
            $result = -1;
//            $header = "From: Admin<info@softwareeasy.it>\n";
//            $oggetto = "errore in UtenteDataManager:insertUtente - utente : " . $_COOKIE["codiceUtente"];
//            $messaggio = " Message : " . $ex->getMessage() . " trace " . $ex->getTraceAsString();
//            mail("casaranese@gmail.com", $oggetto, $messaggio, $header);
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }


    public function getNazioni($connection)
    {
        $lista = array();
        try
        {
            $query = "Select codice, descrizione from Nazioni";
            $stmt = $connection->prepare($query);
            $stmt->execute();

            $stmt->bind_result($codice, $descrizione);
            while ($stmt->fetch())
            {
                $nazione = new Nazione();
                $nazione->codice = $codice;
                $nazione->descrizione = $descrizione;
                array_push($lista, $nazione);
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }
    public function getRegioni($connection)
    {
        $lista = array();
        try
        {
            $query = "Select id, regione from Regioni";
            $stmt = $connection->prepare($query);
            $stmt->execute();

            $stmt->bind_result($id, $regioneDesc);
            while ($stmt->fetch())
            {
                $regione = new Nazione();
                $regione->codice = $id;
                $regione->descrizione = $regioneDesc;
                array_push($lista, $regione);
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }
    public function getPovincie($connection, $regione)
    {
        $lista = array();
        try
        {
            $query = "Select id, provincia from Province where id_regione = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("i", $regione);
            $stmt->execute();

            $stmt->bind_result($id, $provinciaDesc);
            while ($stmt->fetch())
            {
                $provincia = new Nazione();
                $provincia->codice = $id;
                $provincia->descrizione = $provinciaDesc;
                array_push($lista, $provincia);
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }
    public function getComuni($connection, $regione, $provincia)
    {
        $lista = array();
        try
        {
            $query = "Select id, comune from Comuni where id_regione = ? and id_provincia = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("ii", $regione, $provincia);
            $stmt->execute();

            $stmt->bind_result($id, $comuneDesc);
            while ($stmt->fetch())
            {
                $comune = new Nazione();
                $comune->codice = $id;
                $comune->descrizione = htmlspecialchars_decode(htmlentities($comuneDesc));
                array_push($lista, $comune);
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }

    public function getUtenteDiscipline($connection, $codiceUtente)
    {
        $lista = array();
        try
        {
            $query = "Select codiceDisciplina from DisciplinaUtente where codiceUtente = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("i", $codiceUtente);
            $stmt->execute();

            $stmt->bind_result($codiceDisciplina);
            while ($stmt->fetch())
                array_push($lista, $codiceDisciplina);

        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }
    public function utenteHasDisciplina($connection, $codiceUtente, $disciplina)
    {
        $result = -1;
        try
        {
            $query = "Select codiceDisciplina from DisciplinaUtente where codiceUtente = ? and codiceDisciplina = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("ii", $codiceUtente, $disciplina);
            $stmt->execute();

            $stmt->bind_result($codiceDisciplina);
            if ($stmt->fetch())
                $result = 0;

        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

    public function deleteUtenteDisciplina($connection, $codiceUtente, $disciplina)
    {
        $result = 0;
        try
        {
            $query = "delete from DisciplinaUtente where codiceUtente = ? and codiceDisciplina = ?";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("ii", $codiceUtente, $disciplina))
                throw new Exception("Errore in bind_param query : " . $query);
            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        } catch (Exception $ex)
        {
            echo '' . $ex->getMessage();
            $connection->rollback();
            $result = -1;
        }
        if ($stmt != NULL)
        {
            $stmt->close();
        }

        return $result;
    }
    public function getDiscipline($connection)
    {
        $lista = array();
        try
        {
            $query = "Select id, disciplina from Discipline";
            $stmt = $connection->prepare($query);
            $stmt->execute();

            $stmt->bind_result($id, $disciplinaDesc);
            while ($stmt->fetch())
            {
                $disciplina = new Disciplina();
                $disciplina->id = $id;
                $disciplina->disciplina = htmlspecialchars_decode(htmlentities($disciplinaDesc));
                array_push($lista, $disciplina);
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $lista;
    }

    public function getCodiceByEmail($connection, $email, $context)
    {
        $result = new Utente();
        $result->codice = -1;
        try
        {
            $query = "Select codice from Utente where email = ? and context = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("si", $email, $context);
            $stmt->execute();

            $stmt->bind_result($codice);
            if ($stmt->fetch())
                $result->codice = $codice;
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

    public function checkLogin($connection, $email, $password, $context)
    {
        $result = new Utente();
        $result->codice=-2;
        try
        {
            $query = "Select codice, nome, cognome from Utente where email = ? and password = ? and attivo = 1 and context = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("ssi", $email, md5($password), $context);
            $stmt->execute();

            $stmt->bind_result($codice, $nome, $cognome);
            if ($stmt->fetch())
            {
                $result->codice = $codice;
                $result->nome = $nome;
                $result->cognome = $cognome;
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

    public function existOtherOpenSession($connection, $email, $context)
    {
        $result = 0;
        try
        {
            $query = "Select utente from Sessions where email = ? and active = 1 and context = ?";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("si", $email, $context);
            $stmt->execute();

            $stmt->bind_result($utente);
            if ($stmt->fetch())
            {
                $result = $utente;
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

    public function userKickOut($connection, $utente)
    {
        $result = "0";
        try
        {
            $query = "update Sessions set
                     whenCreatedLock = DATE_ADD(SYSDATE(), INTERVAL 5 MINUTE)
                where utente = ?";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("i", $utente))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        }
        catch (Exception $ex)
        {
           echo $ex->getTrace();
            $result = "1";
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

   

    public function updateUserSession($connection, $codiceUtente)
    {
        $result = 0;
        try
        {
            $query = "update Sessions set
                     whenCreatedLock = NOW()
                where codice = ?";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("i", $codiceUtenteGiaLoggato))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        }
        catch (Exception $ex)
        {
            echo $ex->getTrace();
        }
        $result = $stmt->num_rows;
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }

    public function insertUserSession($connection, $email, $context, $codiceUtente)
    {
        try
        {
            $query = "Insert into Sessions (email, utente, context) VALUES(?, ?, ?)";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("sii", $email, $codiceUtente, $context))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        } catch (Exception $ex)
        {
            echo $ex->getMessage();
            $result = -1;
        }
        if ($stmt != NULL)
            $stmt->close();

    }
    
    public function insertSession($connection, $utente)
    {
        try
        {
            $query = "Insert into Sessions (utente) VALUES(?)";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("i", $utente))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        } catch (Exception $ex)
        {
            echo $ex->getMessage();
            $result = -1;
        }
        if ($stmt != NULL)
            $stmt->close();

    }
    
    public function removeSession($connection, $utente)
    {
        try
        {
            $query = "delete from Sessions where utente = ?";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("i", $utente))
                throw new Exception("Errore in bind_param query : " . $query);
            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        }
        catch (Exception $ex)
        {
            echo '' . $ex->getMessage();
        }
        if ($stmt != NULL)
        {
            $stmt->close();
        }
    }
    
    public function getUserInSession($connection, $utente)
    {
      $result = -1;
        try
        {
            $query = "Select utente from Sessions where utente = ? ";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("i", $utente);
            $stmt->execute();

            $stmt->bind_result($utente);
            if ($stmt->fetch())
            {
                $result = 0;
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }
    
    public function insertUserLockedTable($connection, $utente)
    {
      try
        {
            $query = "Insert into UserlockedTable (utente, toDate) VALUES(?, DATE_ADD(SYSDATE(), INTERVAL 2 MINUTE))";
            if (!$stmt = $connection->prepare($query))
                throw new Exception("Errore in prepare query : " . $query);
            if (!$stmt->bind_param("i", $utente))
                throw new Exception("Errore in bind_param query : " . $query);

            if (!$stmt->execute())
                throw new Exception("Errore in execute query : " . $query);
        } catch (Exception $ex)
        {
            echo $ex->getMessage();
            $result = -1;
        }
        if ($stmt != NULL)
            $stmt->close();
    }
    
     public function isUserLocked($connection, $utente)
    {
        $result = -1;
        try
        {
            $query = "Select utente from UserlockedTable where utente = ? and toDate > NOW()";
            $stmt = $connection->prepare($query);
            $stmt->bind_param("i", $utente);
            $stmt->execute();

            $stmt->bind_result($utente);
            if ($stmt->fetch())
            {
                $result = 0;
            }
        } catch (Exception $ex)
        {
            echo $ex->getTrace();
            throw $ex;
        }
        if ($stmt != NULL)
            $stmt->close();

        return $result;
    }
}

?>
